package cn.com.thinvent.dispatch.service.biz.util;

import cn.com.thinvent.dispatch.entity.ModelDatasource;
import cn.com.thinvent.dispatch.entity.response.DataSourceDetailResponse;
import com.alibaba.fastjson.JSON;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DataBaseUtil {

    /**
     * 获取连接
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static Connection getConnection(ModelDatasource modelDatasource) throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://" + modelDatasource.getUrl() + ":" + modelDatasource.getPort() + "/",
                modelDatasource.getUser(), modelDatasource.getPwd());
        return conn;
    }


    /**
     * 获取所有的数据库
     * @param connection
     * @return
     * @throws Exception
     */
    public static List<String> getDataBases(Connection connection) throws Exception {

        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet catalogs = metaData.getCatalogs();
        ArrayList<String> dbs = new ArrayList<>();
        while (catalogs.next()) {
            String db = catalogs.getString(".TABLE_CAT");
            dbs.add(db);
        }

        return dbs;
    }

    /**
     * 获取当前数据库的所有表
     * @param connection 数据库连接对象
     * @param dataBase 数据库名称
     * @return
     * @throws SQLException
     */
    public static List<String> getTables(Connection connection, String dataBase) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
//        最后一个参数TABLE 表示用户表 见 DatabaseMetaData.getTableTypes()方法
        ResultSet resultSet = metaData.getTables(dataBase, null, null, new String[]{"TABLE"});
        ArrayList<String> tables = new ArrayList<>();
        while (resultSet.next()) {
            String table = resultSet.getString("TABLE_NAME");
            tables.add(table);
        }
        return tables;
    }

    /**
     * 获取数据库表的所有字段
     * @param connection 数据库连接对象
     * @param dataBase 数据库名
     * @param tableName 表名
     * @return
     * @throws SQLException
     * @throws IOException
     */
    public static List<String> getTableField(Connection connection, String dataBase, String tableName) throws SQLException, IOException {
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet resultSet = metaData.getColumns(dataBase, null, tableName, null);
        ArrayList<String> columns = new ArrayList<>();
        while (resultSet.next()) {
//            数据库名
            String db_name = resultSet.getString(".TABLE_CAT");
//            表名
            String table_name = resultSet.getString(".TABLE_NAME");
//            获取字段名
            String field = resultSet.getString(".COLUMN_NAME");
//            获取字段类型
            String fieldType = resultSet.getString(".TYPE_NAME");
            String fieldLength = resultSet.getString(".COLUMN_SIZE");
            String fieldDESC = resultSet.getString(".REMARKS");

            columns.add(field);
        }
        return columns;
    }

    public static List<Map<String, Object>> getFieldValues(ModelDatasource modelDatasource, Integer pageNum, Integer pageSize) throws SQLException, ClassNotFoundException {
        // 获取查询的字段
        String fileds = modelDatasource.getFileds();
        String[] split = fileds.split(",");
        Connection connection = getConnectionDML(modelDatasource);
        Statement stmt = connection.createStatement();
        StringBuilder sb = new StringBuilder();
        sb.append("select ");
        sb.append(fileds + " ");
        sb.append("from " + modelDatasource.getDataTable());
        if((pageNum != null && pageNum !=0 ) && (pageSize != null && pageSize != 0)) {
            Integer pageBegin = (pageNum - 1) * pageSize;
            sb.append(" limit " + pageBegin + ", " + pageSize);
        }
        ResultSet rs = stmt.executeQuery(sb.toString());
        List<Map<String, Object>> list = new ArrayList<>();
        while(rs.next()) {
            Map<String, Object> map = new HashMap<>();
            for(String s : split) {
                map.put(s, rs.getObject(s.trim()));
            }
            list.add(map);
        }
        return list;
    }

    private static Connection getConnectionDML(ModelDatasource modelDatasource) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://" + modelDatasource.getUrl() + ":" + modelDatasource.getPort() + "/" + modelDatasource.getDatastore(),
                modelDatasource.getUser(), modelDatasource.getPwd());
        return conn;
    }

    public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
        ModelDatasource modelDatasource = new ModelDatasource();
        modelDatasource.setFileds("task_name, status ");
        modelDatasource.setDataTable("MODEL_TASK");
        modelDatasource.setUrl("192.168.2.88");
        modelDatasource.setPort("3306");
        modelDatasource.setUser("root");
        modelDatasource.setPwd("123456");
        modelDatasource.setDatastore("dere_test");
//
//        List<Map<String, Object>> fieldValues = getFieldValues(modelDatasource);
//        System.out.println(fieldValues);
    }

    public static void getTables(Connection con, String catalog, String schemaPattern, String tableNamePattern) throws SQLException {
        // 获取数据库元数据
        DatabaseMetaData metaData = con.getMetaData();
        // 查询表元数据,pattern可使用通配符查询(%,?)
        ResultSet tableRet = metaData.getTables(
                catalog,
                schemaPattern,
                tableNamePattern,
                new String[]{"TABLE","VIEW"}
        );
        // 遍历表
        while (tableRet.next()) {
            String tableName = tableRet.getString("TABLE_NAME");
            System.out.println("======" + tableName + "======");
            // 查找表字段元数据
            ResultSet colRet = metaData.getColumns(
                    catalog,
                    schemaPattern,
                    tableName,
                    "%"
            );
            // 遍历表字段
            while (colRet.next()) {
                // 字段名
                String columnName = colRet.getString("COLUMN_NAME");
                // 字段类型
                String typeName = colRet.getString("TYPE_NAME");
                // 字段大小
                Integer columnSize = colRet.getInt("COLUMN_SIZE");
                // 数字类型精度值
                Integer decimalDigits = colRet.getInt("DECIMAL_DIGITS");
                // 可为空值
                Boolean nullable = colRet.getBoolean("NULLABLE");
                System.out.println(
                        columnName
                                + ":"
                                + typeName
                                + "(" + columnSize + "," + decimalDigits + ")"
                                + (nullable ? " nullable" : "")
                );
            }
        }
    }
}
